{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NSS Tutorial\n",
    "\n",
    "Field\t| Type\n",
    "--------|-------\n",
    "ukprn\t| varchar(8)\n",
    "institution\t| varchar(100)\n",
    "subject\t| varchar(60)\n",
    "level\t| varchar(50)\n",
    "question\t| varchar(10)\n",
    "A_STRONGLY_DISAGREE\t| int(11)\n",
    "A_DISAGREE\t| int(11)\n",
    "A_NEUTRAL\t| int(11)\n",
    "A_AGREE\t| int(11)\n",
    "A_STRONGLY_AGREE\t| int(11)\n",
    "A_NA\t| int(11)\n",
    "CI_MIN\t| int(11)\n",
    "score\t| int(11)\n",
    "CI_MAX\t| int(11)\n",
    "response\t| int(11)\n",
    "sample\t| int(11)\n",
    "aggregate\t| char(1)\n",
    "\n",
    "National Student Survey 2012\n",
    "\n",
    "The National Student Survey <http://www.thestudentsurvey.com/> is presented to thousands of graduating students in UK Higher Education. The survey asks 22 questions, students can respond with STRONGLY DISAGREE, DISAGREE, NEUTRAL, AGREE or STRONGLY AGREE. The values in these columns represent PERCENTAGES of the total students who responded with that answer.\n",
    "\n",
    "The table `nss` has one row per institution, subject and question."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Check out one row\n",
    "\n",
    "The example shows the number who responded for:\n",
    "\n",
    "- question 1\n",
    "- at 'Edinburgh Napier University'\n",
    "- studying '(8) Computer Science'\n",
    "\n",
    "**Show the the percentage who STRONGLY AGREE**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "nss <- dbReadTable(con, 'nss')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>A_STRONGLY_AGREE</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>23</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " A\\_STRONGLY\\_AGREE\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 23\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| A_STRONGLY_AGREE &lt;int&gt; |\n",
       "|---|\n",
       "| 23 |\n",
       "\n"
      ],
      "text/plain": [
       "  A_STRONGLY_AGREE\n",
       "1 23              "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>% \n",
    "    filter(question=='Q01' & \n",
    "           institution=='Edinburgh Napier University' &\n",
    "           subject=='(8) Computer Science') %>%\n",
    "    select(A_STRONGLY_AGREE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Calculate how many agree or strongly agree\n",
    "\n",
    "**Show the institution and subject where the score is at least 100 for question 15.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 10 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>institution</th><th scope=col>subject</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Kingston College                                                                   </td><td>(I) Education                            </td></tr>\n",
       "\t<tr><td>Royal Holloway, University of London                                               </td><td>(L) Geographical Studies                 </td></tr>\n",
       "\t<tr><td>Solihull College                                                                   </td><td>(I) Education                            </td></tr>\n",
       "\t<tr><td>Stafford College                                                                   </td><td>(D) Business and Administrative studies  </td></tr>\n",
       "\t<tr><td>University of Southampton                                                          </td><td>(E) Mass Communications and Documentation</td></tr>\n",
       "\t<tr><td>University of Wolverhampton                                                        </td><td>(7) Mathematical Sciences                </td></tr>\n",
       "\t<tr><td>University of Leicester                                                            </td><td>(2) Subjects allied to Medicine          </td></tr>\n",
       "\t<tr><td>University of Newcastle upon Tyne                                                  </td><td>(E) Mass Communications and Documentation</td></tr>\n",
       "\t<tr><td>Bishop Grosseteste University College, Lincoln                                     </td><td>(F) Languages                            </td></tr>\n",
       "\t<tr><td>Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk</td><td>(G) Historical and Philosophical studies </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 10 × 2\n",
       "\\begin{tabular}{ll}\n",
       " institution & subject\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Kingston College                                                                    & (I) Education                            \\\\\n",
       "\t Royal Holloway, University of London                                                & (L) Geographical Studies                 \\\\\n",
       "\t Solihull College                                                                    & (I) Education                            \\\\\n",
       "\t Stafford College                                                                    & (D) Business and Administrative studies  \\\\\n",
       "\t University of Southampton                                                           & (E) Mass Communications and Documentation\\\\\n",
       "\t University of Wolverhampton                                                         & (7) Mathematical Sciences                \\\\\n",
       "\t University of Leicester                                                             & (2) Subjects allied to Medicine          \\\\\n",
       "\t University of Newcastle upon Tyne                                                   & (E) Mass Communications and Documentation\\\\\n",
       "\t Bishop Grosseteste University College, Lincoln                                      & (F) Languages                            \\\\\n",
       "\t Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk & (G) Historical and Philosophical studies \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 10 × 2\n",
       "\n",
       "| institution &lt;chr&gt; | subject &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Kingston College                                                                    | (I) Education                             |\n",
       "| Royal Holloway, University of London                                                | (L) Geographical Studies                  |\n",
       "| Solihull College                                                                    | (I) Education                             |\n",
       "| Stafford College                                                                    | (D) Business and Administrative studies   |\n",
       "| University of Southampton                                                           | (E) Mass Communications and Documentation |\n",
       "| University of Wolverhampton                                                         | (7) Mathematical Sciences                 |\n",
       "| University of Leicester                                                             | (2) Subjects allied to Medicine           |\n",
       "| University of Newcastle upon Tyne                                                   | (E) Mass Communications and Documentation |\n",
       "| Bishop Grosseteste University College, Lincoln                                      | (F) Languages                             |\n",
       "| Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk | (G) Historical and Philosophical studies  |\n",
       "\n"
      ],
      "text/plain": [
       "   institution                                                                        \n",
       "1  Kingston College                                                                   \n",
       "2  Royal Holloway, University of London                                               \n",
       "3  Solihull College                                                                   \n",
       "4  Stafford College                                                                   \n",
       "5  University of Southampton                                                          \n",
       "6  University of Wolverhampton                                                        \n",
       "7  University of Leicester                                                            \n",
       "8  University of Newcastle upon Tyne                                                  \n",
       "9  Bishop Grosseteste University College, Lincoln                                     \n",
       "10 Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk\n",
       "   subject                                  \n",
       "1  (I) Education                            \n",
       "2  (L) Geographical Studies                 \n",
       "3  (I) Education                            \n",
       "4  (D) Business and Administrative studies  \n",
       "5  (E) Mass Communications and Documentation\n",
       "6  (7) Mathematical Sciences                \n",
       "7  (2) Subjects allied to Medicine          \n",
       "8  (E) Mass Communications and Documentation\n",
       "9  (F) Languages                            \n",
       "10 (G) Historical and Philosophical studies "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>% \n",
    "    filter(question=='Q15' & score>=100) %>%\n",
    "    select(institution, subject)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Unhappy Computer Students\n",
    "\n",
    "**Show the institution and score where the score for '(8) Computer Science' is less than 50 for question 'Q15'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>institution</th><th scope=col>score</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Blackburn College                                                                  </td><td>45</td></tr>\n",
       "\t<tr><td>North Lindsey College                                                              </td><td>30</td></tr>\n",
       "\t<tr><td>Plymouth College of Art                                                            </td><td>47</td></tr>\n",
       "\t<tr><td>Somerset College of Arts and Technology                                            </td><td>48</td></tr>\n",
       "\t<tr><td>University of Wales, Newport                                                       </td><td>30</td></tr>\n",
       "\t<tr><td>Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk</td><td>45</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 2\n",
       "\\begin{tabular}{ll}\n",
       " institution & score\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Blackburn College                                                                   & 45\\\\\n",
       "\t North Lindsey College                                                               & 30\\\\\n",
       "\t Plymouth College of Art                                                             & 47\\\\\n",
       "\t Somerset College of Arts and Technology                                             & 48\\\\\n",
       "\t University of Wales, Newport                                                        & 30\\\\\n",
       "\t Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk & 45\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 2\n",
       "\n",
       "| institution &lt;chr&gt; | score &lt;int&gt; |\n",
       "|---|---|\n",
       "| Blackburn College                                                                   | 45 |\n",
       "| North Lindsey College                                                               | 30 |\n",
       "| Plymouth College of Art                                                             | 47 |\n",
       "| Somerset College of Arts and Technology                                             | 48 |\n",
       "| University of Wales, Newport                                                        | 30 |\n",
       "| Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk | 45 |\n",
       "\n"
      ],
      "text/plain": [
       "  institution                                                                        \n",
       "1 Blackburn College                                                                  \n",
       "2 North Lindsey College                                                              \n",
       "3 Plymouth College of Art                                                            \n",
       "4 Somerset College of Arts and Technology                                            \n",
       "5 University of Wales, Newport                                                       \n",
       "6 Universities of East Anglia and Essex; Joint Provision at University Campus Suffolk\n",
       "  score\n",
       "1 45   \n",
       "2 30   \n",
       "3 47   \n",
       "4 48   \n",
       "5 30   \n",
       "6 45   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>% \n",
    "    filter(question=='Q15' & \n",
    "           subject=='(8) Computer Science' &\n",
    "           score<50) %>%\n",
    "    select(institution, score)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. More Computing or Creative Students?\n",
    "\n",
    "**Show the subject and total number of students who responded to question 22 for each of the subjects '(8) Computer Science' and '(H) Creative Arts and Design'.**\n",
    "\n",
    "> _HINT_    \n",
    "> You will need to use SUM over the response column and GROUP BY subject"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 2 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>subject</th><th scope=col>tot_response</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>(8) Computer Science        </td><td>10252</td></tr>\n",
       "\t<tr><td>(H) Creative Arts and Design</td><td>33336</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 2 × 2\n",
       "\\begin{tabular}{ll}\n",
       " subject & tot\\_response\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t (8) Computer Science         & 10252\\\\\n",
       "\t (H) Creative Arts and Design & 33336\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 2 × 2\n",
       "\n",
       "| subject &lt;chr&gt; | tot_response &lt;int&gt; |\n",
       "|---|---|\n",
       "| (8) Computer Science         | 10252 |\n",
       "| (H) Creative Arts and Design | 33336 |\n",
       "\n"
      ],
      "text/plain": [
       "  subject                      tot_response\n",
       "1 (8) Computer Science         10252       \n",
       "2 (H) Creative Arts and Design 33336       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>%\n",
    "    filter(question=='Q22' & \n",
    "           subject %in% c(\n",
    "               '(8) Computer Science', '(H) Creative Arts and Design')) %>%\n",
    "    group_by(subject) %>% \n",
    "    summarise(tot_response=sum(response))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Strongly Agree Numbers\n",
    "\n",
    "**Show the subject and total number of students who A_STRONGLY_AGREE to question 22 for each of the subjects '(8) Computer Science' and '(H) Creative Arts and Design'.**\n",
    "\n",
    "> _HINT_    \n",
    "> The A_STRONGLY_AGREE column is a percentage. To work out the total number of students who strongly agree you must multiply this percentage by the number who responded (response) and divide by 100 - take the SUM of that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A grouped_df: 2 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>subject</th><th scope=col>question</th><th scope=col>n_strongly_agree</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>(8) Computer Science        </td><td>Q22</td><td> 3421</td></tr>\n",
       "\t<tr><td>(H) Creative Arts and Design</td><td>Q22</td><td>12108</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 2 × 3\n",
       "\\begin{tabular}{lll}\n",
       " subject & question & n\\_strongly\\_agree\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t (8) Computer Science         & Q22 &  3421\\\\\n",
       "\t (H) Creative Arts and Design & Q22 & 12108\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 2 × 3\n",
       "\n",
       "| subject &lt;chr&gt; | question &lt;chr&gt; | n_strongly_agree &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| (8) Computer Science         | Q22 |  3421 |\n",
       "| (H) Creative Arts and Design | Q22 | 12108 |\n",
       "\n"
      ],
      "text/plain": [
       "  subject                      question n_strongly_agree\n",
       "1 (8) Computer Science         Q22       3421           \n",
       "2 (H) Creative Arts and Design Q22      12108           "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>% \n",
    "    filter(question=='Q22' & \n",
    "           subject %in% c(\n",
    "               '(8) Computer Science', '(H) Creative Arts and Design')) %>% \n",
    "    group_by(subject, question) %>% \n",
    "    mutate(n=response * A_STRONGLY_AGREE) %>%\n",
    "    summarise(n_strongly_agree=round(sum(n)/100, 0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Strongly Agree, Percentage\n",
    "\n",
    "**Show the percentage of students who A_STRONGLY_AGREE to question 22 for the subject '(8) Computer Science' show the same figure for the subject '(H) Creative Arts and Design'.**\n",
    "\n",
    "Use the **ROUND** function to show the percentage without decimal places."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 2 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>subject</th><th scope=col>pct_strongly_agree</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>(8) Computer Science        </td><td>33</td></tr>\n",
       "\t<tr><td>(H) Creative Arts and Design</td><td>36</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 2 × 2\n",
       "\\begin{tabular}{ll}\n",
       " subject & pct\\_strongly\\_agree\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t (8) Computer Science         & 33\\\\\n",
       "\t (H) Creative Arts and Design & 36\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 2 × 2\n",
       "\n",
       "| subject &lt;chr&gt; | pct_strongly_agree &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| (8) Computer Science         | 33 |\n",
       "| (H) Creative Arts and Design | 36 |\n",
       "\n"
      ],
      "text/plain": [
       "  subject                      pct_strongly_agree\n",
       "1 (8) Computer Science         33                \n",
       "2 (H) Creative Arts and Design 36                "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>% \n",
    "    filter(question=='Q22' & \n",
    "           subject %in% c(\n",
    "               '(8) Computer Science', '(H) Creative Arts and Design')) %>%\n",
    "    group_by(subject) %>%\n",
    "    summarise(pct_strongly_agree=round(sum(A_STRONGLY_AGREE * response)/sum(response)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Scores for Institutions in Manchester\n",
    "\n",
    "**Show the average scores for question 'Q22' for each institution that include 'Manchester' in the name.**\n",
    "\n",
    "The column **score** is a percentage - you must use the method outlined above to multiply the percentage by the **response** and divide by the total response. Give your answer rounded to the nearest whole number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>institution</th><th scope=col>tot_score</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Manchester Metropolitan University</td><td>81</td></tr>\n",
       "\t<tr><td>The Manchester College            </td><td>72</td></tr>\n",
       "\t<tr><td>University of Manchester          </td><td>83</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " institution & tot\\_score\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Manchester Metropolitan University & 81\\\\\n",
       "\t The Manchester College             & 72\\\\\n",
       "\t University of Manchester           & 83\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 2\n",
       "\n",
       "| institution &lt;chr&gt; | tot_score &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| Manchester Metropolitan University | 81 |\n",
       "| The Manchester College             | 72 |\n",
       "| University of Manchester           | 83 |\n",
       "\n"
      ],
      "text/plain": [
       "  institution                        tot_score\n",
       "1 Manchester Metropolitan University 81       \n",
       "2 The Manchester College             72       \n",
       "3 University of Manchester           83       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>% \n",
    "    filter(question=='Q22' & \n",
    "           str_detect(institution, 'Manchester')) %>%\n",
    "    group_by(institution) %>%\n",
    "    summarise(tot_score=round(sum(response * score)/sum(response)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.Number of Computing Students in Manchester\n",
    "\n",
    "**Show the institution, the total sample size and the number of computing students for institutions in Manchester for 'Q01'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 3 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>institution</th><th scope=col>tot_sample</th><th scope=col>comp</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Manchester Metropolitan University</td><td>6994</td><td>310</td></tr>\n",
       "\t<tr><td>The Manchester College            </td><td> 537</td><td> 46</td></tr>\n",
       "\t<tr><td>University of Manchester          </td><td>8065</td><td>180</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 3\n",
       "\\begin{tabular}{lll}\n",
       " institution & tot\\_sample & comp\\\\\n",
       " <chr> & <int> & <int>\\\\\n",
       "\\hline\n",
       "\t Manchester Metropolitan University & 6994 & 310\\\\\n",
       "\t The Manchester College             &  537 &  46\\\\\n",
       "\t University of Manchester           & 8065 & 180\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 3\n",
       "\n",
       "| institution &lt;chr&gt; | tot_sample &lt;int&gt; | comp &lt;int&gt; |\n",
       "|---|---|---|\n",
       "| Manchester Metropolitan University | 6994 | 310 |\n",
       "| The Manchester College             |  537 |  46 |\n",
       "| University of Manchester           | 8065 | 180 |\n",
       "\n"
      ],
      "text/plain": [
       "  institution                        tot_sample comp\n",
       "1 Manchester Metropolitan University 6994       310 \n",
       "2 The Manchester College              537        46 \n",
       "3 University of Manchester           8065       180 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nss %>% \n",
    "    filter(question=='Q22' & \n",
    "           str_detect(institution, 'Manchester')) %>%\n",
    "    mutate(comp=case_when(\n",
    "        subject=='(8) Computer Science' ~ sample,\n",
    "        TRUE                            ~ 0L)) %>%\n",
    "    group_by(institution) %>%\n",
    "    summarise(tot_sample=sum(sample), comp=sum(comp))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
